using System;
using System.Text;
using TccAg.VO;
using System.Data;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;

namespace TccAg.DA
{
    public class GradehorariaDA
    {
        private String queryInsert = "INSERT INTO gradehoraria( NOTA, CARGAHORARIA, IDGRADE, CRT_1, CRT_2, CRT_3, CRT_4, CRT_5, SEMESTRE) VALUES( @nota, @cargahoraria, @idGrade, @crt_1, @crt_2, @crt_3, @crt_4, @crt_5, @Semestre)";
        private String queryUpdate = "UPDATE gradehoraria SET nota = @nota, cargahoraria = @cargahoraria, idGrade = @idGrade, crt_1 = @crt_1, crt_2 = @crt_2, crt_3 = @crt_3, crt_4 = @crt_4, crt_5 = @crt_5, Semestre = @Semestre WHERE ID = @id";
        private String queryDelete = "DELETE FROM gradehoraria WHERE ID = @id";
        private String queryLoadAll = "SELECT  id, nota, cargahoraria, idGrade, crt_1, crt_2, crt_3, crt_4, crt_5, Semestre FROM gradehoraria";
        private String queryLoadByPk = "SELECT  id, nota, cargahoraria, idGrade, crt_1, crt_2, crt_3, crt_4, crt_5, Semestre FROM gradehoraria WHERE ID = @id";
        private String queryLoadByFk = "SELECT  id, nota, cargahoraria, idGrade, crt_1, crt_2, crt_3, crt_4, crt_5, Semestre FROM gradehoraria WHERE IDGRADE = @idGrade, SEMESTRE = @Semestre";
        private SqlConnection conexao = new SqlConnection("Pegar conexão");

        //INSERIR 
        public int inserir(GradehorariaVO gradehoraria)
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryInsert;

                if (gradehoraria.Nota != 0)
                        cmd.Parameters.Add(new SqlParameter("@nota", (object) gradehoraria.Nota));
                else
                        cmd.Parameters.Add(new SqlParameter("@nota", (object) DBNull.Value));

                if (gradehoraria.Cargahoraria != 0)
                        cmd.Parameters.Add(new SqlParameter("@cargahoraria", (object) gradehoraria.Cargahoraria));
                else
                        cmd.Parameters.Add(new SqlParameter("@cargahoraria", (object) DBNull.Value));

                if (gradehoraria.Idgrade != 0)
                        cmd.Parameters.Add(new SqlParameter("@idGrade", (object) gradehoraria.Idgrade));
                else
                        cmd.Parameters.Add(new SqlParameter("@idGrade", (object) DBNull.Value));

                if (gradehoraria.Crt1 != 0)
                        cmd.Parameters.Add(new SqlParameter("@crt_1", (object) gradehoraria.Crt1));
                else
                        cmd.Parameters.Add(new SqlParameter("@crt_1", (object) DBNull.Value));

                if (gradehoraria.Crt2 != 0)
                        cmd.Parameters.Add(new SqlParameter("@crt_2", (object) gradehoraria.Crt2));
                else
                        cmd.Parameters.Add(new SqlParameter("@crt_2", (object) DBNull.Value));

                if (gradehoraria.Crt3 != 0)
                        cmd.Parameters.Add(new SqlParameter("@crt_3", (object) gradehoraria.Crt3));
                else
                        cmd.Parameters.Add(new SqlParameter("@crt_3", (object) DBNull.Value));

                if (gradehoraria.Crt4 != 0)
                        cmd.Parameters.Add(new SqlParameter("@crt_4", (object) gradehoraria.Crt4));
                else
                        cmd.Parameters.Add(new SqlParameter("@crt_4", (object) DBNull.Value));

                if (gradehoraria.Crt5 != 0)
                        cmd.Parameters.Add(new SqlParameter("@crt_5", (object) gradehoraria.Crt5));
                else
                        cmd.Parameters.Add(new SqlParameter("@crt_5", (object) DBNull.Value));

                if (gradehoraria.Semestre.Id != 0)
                        cmd.Parameters.Add(new SqlParameter("@Semestre", (object) gradehoraria.Semestre.Id));
                else
                        cmd.Parameters.Add(new SqlParameter("@Semestre", (object) DBNull.Value));

                conexao.Open();
                return cmd.ExecuteNonQuery();

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //ATUALIZAR 
        public int atualizar(GradehorariaVO gradehoraria)
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                if (gradehoraria.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@id", (object)gradehoraria.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryUpdate;

                if (gradehoraria.Nota != 0)
                        cmd.Parameters.Add(new SqlParameter("@nota", (object) gradehoraria.Nota));
                else
                        cmd.Parameters.Add(new SqlParameter("@nota", (object) DBNull.Value));
                if (gradehoraria.Cargahoraria != 0)
                        cmd.Parameters.Add(new SqlParameter("@cargahoraria", (object) gradehoraria.Cargahoraria));
                else
                        cmd.Parameters.Add(new SqlParameter("@cargahoraria", (object) DBNull.Value));
                if (gradehoraria.Idgrade != 0)
                        cmd.Parameters.Add(new SqlParameter("@idGrade", (object) gradehoraria.Idgrade));
                else
                        cmd.Parameters.Add(new SqlParameter("@idGrade", (object) DBNull.Value));
                if (gradehoraria.Crt1 != 0)
                        cmd.Parameters.Add(new SqlParameter("@crt_1", (object) gradehoraria.Crt1));
                else
                        cmd.Parameters.Add(new SqlParameter("@crt_1", (object) DBNull.Value));
                if (gradehoraria.Crt2 != 0)
                        cmd.Parameters.Add(new SqlParameter("@crt_2", (object) gradehoraria.Crt2));
                else
                        cmd.Parameters.Add(new SqlParameter("@crt_2", (object) DBNull.Value));
                if (gradehoraria.Crt3 != 0)
                        cmd.Parameters.Add(new SqlParameter("@crt_3", (object) gradehoraria.Crt3));
                else
                        cmd.Parameters.Add(new SqlParameter("@crt_3", (object) DBNull.Value));
                if (gradehoraria.Crt4 != 0)
                        cmd.Parameters.Add(new SqlParameter("@crt_4", (object) gradehoraria.Crt4));
                else
                        cmd.Parameters.Add(new SqlParameter("@crt_4", (object) DBNull.Value));
                if (gradehoraria.Crt5 != 0)
                        cmd.Parameters.Add(new SqlParameter("@crt_5", (object) gradehoraria.Crt5));
                else
                        cmd.Parameters.Add(new SqlParameter("@crt_5", (object) DBNull.Value));
                if (gradehoraria.Semestre.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@Semestre", (object)gradehoraria.Semestre.Id));
                else
                        cmd.Parameters.Add(new SqlParameter("@Semestre", (object) DBNull.Value));
                conexao.Open();
                return cmd.ExecuteNonQuery();

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //EXCLUIR 
        public int excluir(GradehorariaVO gradehoraria)
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryDelete;

                if (gradehoraria.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@id", (object)gradehoraria.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                conexao.Open();
                return cmd.ExecuteNonQuery();

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //LOAD ALL 
        public List<GradehorariaVO> loadAll()
        {
            try
            {
                List<GradehorariaVO> objRetorno = new List<GradehorariaVO>();
                GradehorariaVO objTemp;
                SqlCommand cmd;
                SqlDataReader dr;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadAll;

                conexao.Open();
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        objTemp = new GradehorariaVO();
                        if (dr["id"] != DBNull.Value)
                           objTemp.Id = (int)dr["id"];

                        if (dr["nota"] != DBNull.Value)
                           objTemp.Nota = (Double)dr["nota"];

                        if (dr["cargahoraria"] != DBNull.Value)
                           objTemp.Cargahoraria = (int)dr["cargahoraria"];

                        if (dr["idGrade"] != DBNull.Value)
                           objTemp.Idgrade = (int)dr["idGrade"];

                        if (dr["crt_1"] != DBNull.Value)
                           objTemp.Crt1 = (Double)dr["crt_1"];

                        if (dr["crt_2"] != DBNull.Value)
                           objTemp.Crt2 = (Double)dr["crt_2"];

                        if (dr["crt_3"] != DBNull.Value)
                           objTemp.Crt3 = (Double)dr["crt_3"];

                        if (dr["crt_4"] != DBNull.Value)
                           objTemp.Crt4 = (Double)dr["crt_4"];

                        if (dr["crt_5"] != DBNull.Value)
                           objTemp.Crt5 = (Double)dr["crt_5"];

                        if (dr["Semestre"] != DBNull.Value)
                        {
                            SemestreVO param = new SemestreVO();
                            param.Id = (int)dr["Semestre"];
                            objTemp.Semestre = new SemestreDA().loadByPk(param);
                        }

                        objRetorno.Add(objTemp);
                    }
                }
                return objRetorno;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //LOAD BY PK - Passa um objeto do tipo VO para em casos onde usar chave primária composta ter essas propriedades preenchidas
        public GradehorariaVO loadByPk(GradehorariaVO gradehoraria)
        {
            try
            {
                GradehorariaVO objRetorno = null;
                GradehorariaVO objTemp;
                SqlCommand cmd;
                SqlDataReader dr;
                cmd = conexao.CreateCommand();
                if (gradehoraria.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@id", (object)gradehoraria.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadByPk;

                conexao.Open();
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    dr.Read();
                    {
                        objTemp = new GradehorariaVO();
                        if (dr["id"] != DBNull.Value)
                           objTemp.Id = (int)dr["id"];

                        if (dr["nota"] != DBNull.Value)
                           objTemp.Nota = (Double)dr["nota"];

                        if (dr["cargahoraria"] != DBNull.Value)
                           objTemp.Cargahoraria = (int)dr["cargahoraria"];

                        if (dr["idGrade"] != DBNull.Value)
                           objTemp.Idgrade = (int)dr["idGrade"];

                        if (dr["crt_1"] != DBNull.Value)
                           objTemp.Crt1 = (Double)dr["crt_1"];

                        if (dr["crt_2"] != DBNull.Value)
                           objTemp.Crt2 = (Double)dr["crt_2"];

                        if (dr["crt_3"] != DBNull.Value)
                           objTemp.Crt3 = (Double)dr["crt_3"];

                        if (dr["crt_4"] != DBNull.Value)
                           objTemp.Crt4 = (Double)dr["crt_4"];

                        if (dr["crt_5"] != DBNull.Value)
                           objTemp.Crt5 = (Double)dr["crt_5"];

                        if (dr["Semestre"] != DBNull.Value)
                        {
                            SemestreVO param = new SemestreVO();
                            param.Id = (int)dr["Semestre"];
                            objTemp.Semestre = new SemestreDA().loadByPk(param);
                        }

                        objRetorno = objTemp;
                    }
                }
                return objRetorno;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //LOAD BY FK - Passa um objeto do tipo VO para em casos onde usar chave estrangeira composta ter essas propriedades preenchidas
        public List<GradehorariaVO> loadByFk(GradehorariaVO gradehoraria)
        {
            try
            {
                List<GradehorariaVO> objRetorno = new List<GradehorariaVO>();
                GradehorariaVO objTemp;
                SqlCommand cmd;
                SqlDataReader dr;
                cmd = conexao.CreateCommand();
                if (gradehoraria.Idgrade != 0)
                    cmd.Parameters.Add(new SqlParameter("@idGrade", (object)gradehoraria.Idgrade));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                if (gradehoraria.Semestre.Id != 0)
                    cmd.Parameters.Add(new SqlParameter("@Semestre", (object)gradehoraria.Semestre.Id));
                else
                    throw new Exception("Parâmetro necessário não enviado para o DA");

                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadByFk;

                conexao.Open();
                dr = cmd.ExecuteReader();

                if (dr.HasRows)
                {
                    dr.Read();
                    {
                        objTemp = new GradehorariaVO();
                        if (dr["id"] != DBNull.Value)
                           objTemp.Id = (int)dr["id"];

                        if (dr["nota"] != DBNull.Value)
                           objTemp.Nota = (Double)dr["nota"];

                        if (dr["cargahoraria"] != DBNull.Value)
                           objTemp.Cargahoraria = (int)dr["cargahoraria"];

                        if (dr["idGrade"] != DBNull.Value)
                           objTemp.Idgrade = (int)dr["idGrade"];

                        if (dr["crt_1"] != DBNull.Value)
                           objTemp.Crt1 = (Double)dr["crt_1"];

                        if (dr["crt_2"] != DBNull.Value)
                           objTemp.Crt2 = (Double)dr["crt_2"];

                        if (dr["crt_3"] != DBNull.Value)
                           objTemp.Crt3 = (Double)dr["crt_3"];

                        if (dr["crt_4"] != DBNull.Value)
                           objTemp.Crt4 = (Double)dr["crt_4"];

                        if (dr["crt_5"] != DBNull.Value)
                           objTemp.Crt5 = (Double)dr["crt_5"];

                        if (dr["Semestre"] != DBNull.Value)
                        {
                            SemestreVO param = new SemestreVO();
                            param.Id = (int)dr["Semestre"];
                            objTemp.Semestre = new SemestreDA().loadByPk(param);
                        }

                        objRetorno.Add(objTemp);
                    }
                }
                return objRetorno;
            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

        //DATATABLE LOAD ALL 
        public DataTable dataTableLoadAll()
        {
            try
            {
                SqlCommand cmd;
                cmd = conexao.CreateCommand();
                cmd.CommandType = System.Data.CommandType.Text;
                cmd.CommandText = queryLoadAll;

                conexao.Open();
                SqlDataAdapter dtAdpt = new SqlDataAdapter(cmd);
                DataTable dtTbl = new DataTable("tab_gradehoraria");

                dtAdpt.Fill(dtTbl);
                return dtTbl;

            }
            catch (Exception)
            {
                throw;
            }
            finally
            {
                if (conexao.State == System.Data.ConnectionState.Open) conexao.Close();
            }
        }

    }
}
